package com.my.easypoi.test.simple;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.json.JSONUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.my.core.api.R;
import com.my.core.util.map.GaoDeMapUtil;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * @author timo
 * @date 2022/6/16
 */
@RestController
public class controller {
//    https://blog.csdn.net/weixin_45003796/article/details/121837097
    @PostMapping("/exportData")
    public R exportDataForExcel(@RequestBody List<Map<String, Object>> dataList,
                                HttpServletResponse response) throws IOException {
        if (CollUtil.isNotEmpty(dataList)) {
            // 通过工具类创建writer，默认创建xls格式
            ExcelWriter writer = ExcelUtil.getWriter();
            writer.write(dataList, true);
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=test.xls");
            ServletOutputStream out = response.getOutputStream();
            writer.flush(out);
            writer.close();
            return null;
        } else {
            return R.fail("表无数据！");
        }
    }
//    @PostMapping("/export")
//    public Result<String> exportData(@RequestBody List<Map<String, Object>> dataList) {
//        if (CollUtil.isNotEmpty(dataList)) {
//            // 通过hutool工具类创建writer，默认创建xls格式
//            ExcelWriter writer = ExcelUtil.getWriter("E:\test\test.xls");
//            //将list map数据写出文件
//            writer.write(dataList, true);
//            writer.close();
//        } else {
//            return ResultUtil.failure("表无数据！");
//        }
//    }

//    读取为Map列表
//    private List<Map<String,Object>> readerExcelInfo(String excelUrl){
//        List<Map<String,Object>> result = new ArrayList<>();
//        if(excelUrl.isEmpty()){
//            return Collections.emptyList();
//        }
//        try {
//            ExcelReader excelReader = ExcelUtil.getReader(new File(excelUrl));
//            //读取为Map列表，默认第一行为标题行，Map中的key为标题，value为标题对应的单元格值。
//            result = excelReader.readAll();
//        }catch (Exception e){
//            e.printStackTrace();
//        }
//        return result;
//    }

    ///**
    //* startRowIndex 起始行（包含，从0开始计数）
    //*/
    //read(int startRowIndex);
    ///**
    //* startRowIndex  起始行（包含，从0开始计数）
    //* endRowIndex    读取结束行（包含，从0开始计数）
    //*/
    //read(int startRowIndex, int endRowIndex);
    ///**
    //* startRowIndex 起始行（包含，从0开始计数）
    //* endRowIndex   结束行（包含，从0开始计数）
    //* aliasFirstLine 是否首行作为标题行转换别名
    //*/
    //read(int startRowIndex, int endRowIndex, boolean aliasFirstLine);
    ///**
    //* beanType  每行对应Bean的类型
    //*/
    //readAll(Class<T> beanType);
    //————————————————
    //版权声明：本文为CSDN博主「Y呀」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
    //原文链接：https://blog.csdn.net/weixin_45003796/article/details/121837097

    /**
     * excel转json
     * @param file
     * @return
     * @throws Exception
     */
    //    @Comment("excelToJson")
    @RequestMapping("excelToJson")
    public JSONObject excelToJson(@RequestParam("file")/*@Comment("file")*/ MultipartFile file) throws Exception {
        final int START_ROW = 1;
        InputStream inputStream = file.getInputStream();
        Workbook wb = WorkbookFactory.create(inputStream);
        int numberOfSheets = wb.getNumberOfSheets();
        JSONObject jsonObject = new JSONObject();
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = wb.getSheetAt(i);
            //excel问题，行数获取错误
//            int rows = sheet.getPhysicalNumberOfRows();
            int rows = 1010;
            if (rows <= START_ROW) {
                continue;
            }
            //获取表头行
            Row rowFirst = sheet.getRow(0);
            int cells = rowFirst.getPhysicalNumberOfCells();
            JSONArray jsonArray = new JSONArray();
            for (int r = START_ROW; r < rows; r++) {
                Row row = sheet.getRow(r);
                if (row != null) {
                    // 存储这一行中每一列的数据
                    JSONObject rowObj = new JSONObject();
                    // 循环遍历每一列
                    for (int j = 0; j < cells; j++) {
                        Cell cellData = row.getCell(j);
                        // 这一列存在
                        if (cellData != null) {
                            switch (cellData.getCellTypeEnum()) {
                                case NUMERIC: {
                                    rowObj.put(rowFirst.getCell(j).getStringCellValue(), cellData.getNumericCellValue());
                                    break;
                                }
                                case FORMULA: {
                                    if (DateUtil.isCellDateFormatted(cellData)) {
                                        // 日期
                                        rowObj.put(rowFirst.getCell(j).getStringCellValue(), cellData.getDateCellValue());
                                    } else {
                                        // 纯数字
                                        rowObj.put(rowFirst.getCell(j).getStringCellValue(), cellData.getNumericCellValue());
                                    }
                                    break;
                                }
                                case STRING: {
                                    rowObj.put(rowFirst.getCell(j).getStringCellValue(), cellData.getStringCellValue());
                                    break;
                                }
                                case BLANK:
                                    break;
                                default:
                                    System.err.println("不识别的类型" + cellData.getCellTypeEnum());
                                    rowObj.put(rowFirst.getCell(j).getStringCellValue(), "");
                                    break;
                            }
                        } else {
                            rowObj.put(rowFirst.getCell(j).getStringCellValue(), "");
                        }
                    }
                    jsonArray.add(rowObj);
                }
            }
            jsonObject.put("sheet" + i, jsonArray);
        }
        System.out.println(jsonObject);
        return jsonObject;
    }

    /**
     * 给地址添加经纬度
     *
     * @param file
     * @return
     * @throws Exception
     */
    @RequestMapping("addrJsonAddXY")
    public Object addrJsonAddXY(@RequestParam("file") MultipartFile file)
            throws Exception {
        byte[] bytes = new byte[1024];
        InputStream inputStream = file.getInputStream();
        StringBuilder sb = new StringBuilder();

        int read = 0;
        while((read = inputStream.read(bytes)) != -1) {
            sb.append(new String(bytes, 0, read,"UTF-8"));
        };
            cn.hutool.json.JSONObject object = JSONUtil.parseObj(sb);

        cn.hutool.json.JSONArray sheet0 = object.getJSONArray("sheet0");

        List returnLis = new ArrayList();

        Iterator<Object> iterator = sheet0.iterator();
        while (iterator.hasNext()){
            Object nextObj = iterator.next();
            cn.hutool.json.JSONObject next = JSONUtil.parseObj(nextObj);
            Map returnMap =new HashMap();
            returnMap.put("no",next.getStr("no"));

            // 地址名称
            String address = next.getStr("addr");
            // 刚刚申请的key。一定要换成你自己的key！！！！！！！
            String key = "12";
            // 调用自己写好的封装方法
            JSONObject positionObj = GaoDeMapUtil.getLngAndLat(address, key);
            String longitude = positionObj.getString("longitude");
            String latitude = positionObj.getString("latitude");
//            System.out.println("经度：" + longitude);
//            System.out.println("纬度：" + latitude);
            returnMap.put("x",longitude);
            returnMap.put("y",latitude);
            returnLis.add(returnMap);
        }
        return returnLis;
    }
}